package com.hnkywl.jip.dao;

import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.SQLQuery;
import org.springframework.stereotype.Repository;

import java.util.*;

/**
 * Created with IntelliJ IDEA.
 * User: jerry
 * Date: 13-2-26
 * Time: 下午2:14
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class BankLoanDao extends SimpleHibernateTemplate {
    /**
     * @param companyId 公司ID
     * @return
     */
    public Page<Object[]> findBankLoan(Integer companyId, Integer status, String voucherNum, Integer limit, Integer start) {
        // sql查询字段
        //单据号码 收款日期 收款类型 客户名称 收款金额 票据号码 入帐银行 类型 审核人 公司名称 凭证号
        String sqlColumn = "select xxtype=1,xxtypename='银行借款',b.compid, " +
                " compname=(dbo.gf_compid_shortname(b.compid)),xxid=a.jkid, " +
                " dt=convert(varchar(10),a.dt,102), custname=(dbo.gf_custid_name(a.custid)), bankname=(select shortname from t_cw_bank where bankid=a.bankid), " +
                " je, note, zdr=(select name from t_crm_man where manid=a.zdr),a.u8vouchid, " +
                " isins=a.u8insflag,isdel=(case when a.u8insflag=3 and a.u8delflag=1 then 1 else 0 end), " +
                " isignseq=isnull(isignseq,0),iperiod=isnull(iperiod,0),a.u8cnt,isreset=(case when a.u8insflag=2 or a.u8delflag=2 then 1 else 0 end), " +
                " operation=(case when :status in (1,4,5,9) then 'add' when :status=7 then 'delete' when  :status in (6,8) then 'reset' else 'none' end) " +
                "from t_cw_jk a,t_sys_comp b " +
                "where a.status<2 " +
                (voucherNum != null && !voucherNum.isEmpty() ? " and a.jiuqi_voucher_num=:voucherNum " : "") +
                (companyId != null && companyId != 0 ? " and a.compid=:compid " : "") +
                " and a.type=1  and a.compid=b.compid " +
                " and ((:status in (1,2,3) and a.u8insflag=:status and isnull(a.jiuqi_insert_flag,1)=1) " +
                " or (:status=4 and a.u8delflag=2) " +
                " or (:status=5 and a.u8delflag=3)" +
                " or (:status in(6,7) and a.jiuqi_insert_flag=:status-4)" +
                " or (:status=8 and a.jiuqi_delete_flag=2)" +
                " or (:status=9 and a.jiuqi_delete_flag=3)) " +
                "union all " +
                "select xxtype=2, xxtypename='银行还款',b.compid, " +
                " compname=(dbo.gf_compid_shortname(b.compid)),xxid=c.pkid,dt=convert(varchar(10),c.dt,102), " +
                " custname=(dbo.gf_custid_name(a.custid)), bankname=(select shortname from t_cw_bank where bankid=a.bankid), " +
                " c.je, c.note,zdr=(select name from t_crm_man where manid=c.zdr),c.u8vouchid, " +
                " isins=c.u8insflag,isdel=(case when c.u8insflag=3 and c.u8delflag=1 then 1 else 0 end), " +
                " isignseq=isnull(c.isignseq,0),iperiod=isnull(c.iperiod,0),c.u8cnt,isreset=(case when c.u8insflag=2 or c.u8delflag=2 then 1 else 0 end), " +
                " operation=(case when :status in (1,4,5,9) then 'add' when :status=7 then 'delete' when  :status in (6,8) then 'reset' else 'none' end) " +
                "from t_cw_jk a inner join t_sys_comp b on a.compid=b.compid inner join t_cw_bank_dtl c on a.jkid=c.xxid and c.xxtype=2 " +
                "where a.status<2 " +
                " and ((:status in (1,2,3) and a.u8insflag=:status and isnull(a.jiuqi_insert_flag,1)=1) " +
                " or (:status=4 and a.u8delflag=2) " +
                " or (:status=5 and a.u8delflag=3)" +
                " or (:status in(6,7) and a.jiuqi_insert_flag=:status-4)" +
                " or (:status=8 and a.jiuqi_delete_flag=2)" +
                " or (:status=9 and a.jiuqi_delete_flag=3)) " +
                (voucherNum != null && !voucherNum.isEmpty() ? " and a.jiuqi_voucher_num=:voucherNum " : "") +
                (companyId != null && companyId != 0 ? " and a.compid=:compid " : "") +
                " and a.type=1 ";

//        查询记录数
        String sqlCount = "select count(1) from (" + sqlColumn + ") as count";
        String orderCondition = " order by dt";
        //获取总记录数
        SQLQuery query = createSQLQuery(sqlCount);

        query.setInteger("status", status);
        if (companyId != null)
            query.setInteger("compid", companyId);
        if (voucherNum != null && !voucherNum.isEmpty())
            query.setString("voucherNum", voucherNum);
        Integer total = (Integer) query.uniqueResult();
        List<Object[]> result = null;

        if (total > 0) {
            //获取列表数据
            query = createSQLQuery(sqlColumn + orderCondition);

            query.setInteger("status", status);
            if (companyId != null)
                query.setInteger("compid", companyId);
            if (voucherNum != null && !voucherNum.isEmpty())
                query.setString("voucherNum", voucherNum);
            result = query.setFirstResult(start).setMaxResults(limit).list();
        }
        //返回值
        Page<Object[]> page = new Page<Object[]>();
        page.setTotalCount(total);
        if (result != null && !result.isEmpty())
            page.setResult(result);
        return page;
    }

    public List getVoucher(Integer id, Integer xxtype) {
        String sql = "{call sp_jiuqi_jkinput_list(?,?,?,?)}";
        List<Object[]> list = createSQLQuery(sql, xxtype, id, null, null).list();
        return list;
    }

    /**
     * 收付款凭证主表信息
     *
     * @param id 收付款ID
     */
    public List getVoucherMaster(Integer id) {
        String sql = "select dbo.gf_cw_sys_csign(dbo.gf_sys_cwcompid_by_compid(k.compid)," +
                "dbo.gf_cw_sys_isignseq(k.compid,2)),c.company_name,c.company_code " +
                "from t_jiuqi_company c,t_cw_kp k where c.company_id=k.compid and k.kpid=?";
        Object[] result = (Object[]) createSQLQuery(sql, id).uniqueResult();
        if (result != null)
            return Arrays.asList(result);
        return null;
    }

    public List getRevokeInfo(Integer id, Integer xxtype) {
        String sql = "select k.jiuqi_uuid,k.jiuqi_voucher_id,c.company_code from t_cw_jk k,t_jiuqi_company c where k.compid=c.company_id and k.jkid=?";
        Object[] result = (Object[]) createSQLQuery(sql, id).uniqueResult();
        if (result != null)
            return Arrays.asList(result);
        return null;
    }

}
